--新增学校信息
insert into Shl_info (SHL_ID, SHL_NM, SHL_LOGO, CARD_AGREEMENT, KEY1, KEY2, YKT_OPEN_FLG, YKT_ID, YKT_NM, YKT_DESKEY, YKT_DESKEY_OUT, YKT_PUBKEY,
                      YKT_PRIKEY, TRDPT_PLTFRM_ORCD, SIGN_INSID,   PAY_CHECK_FLAG, FRESH_RESULT_FLAG, FRESH_RESULT_TIME,
                      FRESH_CODE_TIME, QB_MODULE, RCRD_TYPE, SERVICEURL_OUT, ACCESSTOKEN_OUT, TOKENTIME_OUT, SIGNKEY_OUT, CHECKSIGNKEY_OUT,  WHITE_FLAG,SHL_ADCODE,
                      SHL_CITYCODE,SHL_LATITUDE, SHL_LONGITUDE,LIMIT_PER_TRAN, LIMIT_PER_DAY, SHL_ADDR, SCHOOL_CLASSES , TYPE,AppId_out,CustomerCode_out,proxy_model,out_adapter_model )
values ('学校编码', '学校名称', '学校logo图片地址', '电子校园卡开通协议编号', '门禁消费码密钥1','门禁消费码密钥2', '', '一卡通厂商编号','一卡通厂商名称',
        '一卡通商户交易密钥', '一卡通交易密钥out', '', '', '','签约机构编码',  '0', '1', '3', '60','00','00',
        '接出URL地址', '接出签名私钥', '', '', '', '', '所属机构编码', '分行号', '学校纬度', '学校经度', '1000','1000','学校地址','学校类型','','接出应用编号','接出学校编号','代理模式','接出模式');

--新增渠道信息
insert into thdpty_App_info (APP_ID, SHL_ID, SHL_NM, APP_NM, TITLE, CATEGORY,  OPEN_FLG, PAY_CHECK_FLAG, RCRD_ID_WAY, CHK_COOKIE_FLAG, YKT_CZ_WAY,WXXCX_FLAG)
values ('渠道ID', '学校编码', '学校名称', '渠道名称', '是否有title标题栏', 'APP标志',  '开通状态', '消费检查标志', '钱包消费码获取方式', '登陆是否检查cookie', '一卡通充值方式位图','是否微信小程序原生');

--新增卡片信息
insert into card_tb(Card_TPCD,CARD_NM,SHL_ID,CARD_PIC,ARBG,FONT_COLOR) values('卡类型ID','卡类别名称','学校编码','卡片图片路径','透明度颜色','字体颜色');

--新增学校功能菜单
insert into SCHL_MODEL_TB(SHL_ID,APP_ID,MODEL_ID,Card_TPCD,MODEL_Wight,Display_type) values('学校编码','APP_ID','菜单ID','卡类型ID','菜单权重','显示类型');

--插入申卡白名单数据
insert into DZK_APPLYBANKCARD_WHITELIST(SHL_ID,ST_ID,CRDT_NO,CARD_TPCD,CST_NM,MBLPH_NP,COLLEGE,MAJOR,CLASS)  values('学校编码','学工号','身份证号','卡类型ID','姓名','手机号','学院','专业','班级');

--新增学校银行卡类型维护
insert into stcust_applybankcard_cardtype_shl values ('学校编码','银行卡种编号','是否默认选中，0-否，1-是')

--新增菜单
insert into MODEL_TB(MODEL_ID,MODEL_NM,CATAGORY,ICON,PAGE) values('菜单ID','菜单名称','分类','菜单图标','URL链接');



insert into SCHL_MODEL_TB(SHL_ID,APP_ID,MODEL_ID,Card_TPCD) values('学校编码','APP_ID','菜单ID','00');
--新增银行卡类型维护(若有新增的银行卡则配置，否则不配置)
    insert into stcust_applybankcard_cardtype(CARD_ID,card_name,pic_url,card_desc) values ('银行卡种编号','银行卡名称','卡片的路径','卡片的描述');



--OAUTH2_INFO表新增APP_SECRET，MODE,APP_TOKEN字段
alter table OAUTH2_INFO add (
    APP_SECRET VARCHAR2(256), -- 应用秘钥
    MODE VARCHAR2(64), -- 模式，区分渠道
    APP_TOKEN VARCHAR2(64)  --应用授权令牌
);


--新增闽政通渠道信息
insert into thdpty_App_info (APP_ID, SHL_ID, SHL_NM, APP_NM, TITLE, CATEGORY,  OPEN_FLG, PAY_CHECK_FLAG, RCRD_ID_WAY, CHK_COOKIE_FLAG, YKT_CZ_WAY,WXXCX_FLAG)
values ('闽政通渠道ID', '学校编码', '学校名称', '闽政通', '是否有title标题栏', 'APP标志',  '开通状态', '消费检查标志', '钱包消费码获取方式', '登陆是否检查cookie', '一卡通充值方式位图','是否微信小程序原生');


--下线闽政通渠道
UPDATE thdpty_App_info SET open_flg = '0' WHERE SHL_ID='学校编码' and APP_ID='闽政通渠道ID';


--创建钱包解控流水表
CREATE TABLE unlock_wallet_flow(
    flow_id varchar2(32) primary key, --解控流水id
    Shl_id varchar2(64) not null, --学校id
    St_id varchar2(64) not null, --解控的学工号
    wallet_id varchar2(64) ,  --电子钱包账号
    migration_flag varchar2(1),  --迁移标志
    Unlock_status varchar2(32),  --解控状态
    Errcode varchar2(64),    --错误码
    errmsg varchar2(1024) ,  --错误信息
    operator_id varchar2(64),  --解控操作员ID
    operator_time varchar2(14),  --解控时间yyyyMMddhhmmss
    operator_chanl varchar2(2)    --解控渠道
    );

--对账文件管理信息表新增唯一索引：shl_id+Bill_file_date
create UNIQUE index bill_file_infoUNIQUEIndex on bill_file_info(shl_id,Bill_file_date);



select * from subsidy_config where shl_id='temp6982091' and DEVICE_ID in('145D002005000257','147D002005000126','147D002005000143');
--没有维护则插入
insert into subsidy_config(shl_id,device_id,card_tpcd,subsidy_flag,update_time)
select shl_id,TERM_ID,'00','1','20240904' from mer_info where shl_id='temp6982091' and
    TERM_ID in('145D002005000257','147D002005000126','147D002005000143');
--维护机具权限
insert into
    device_auth(shl_id,device_id,scene_type,enabled_state,create_time)
values('temp6982091','145D002005000257','1','1',to_date('2024-11-29
11:32:00','yyyy-mm-dd hh24:mi:ss'));


--首页权限位图
insert into yx_param(SHL_ID,APP_ID,PARAM_NO,PARAM_VALUE,PARAM_DESC,PARAM_TYPE,USING) values ('10486','*','BITMAP','0','首页权限位图','00','0');
insert into yx_param(SHL_ID,APP_ID,PARAM_NO,PARAM_VALUE,PARAM_DESC,PARAM_TYPE,USING) values ('43100002','*','BITMAP','1011101111111111111','首页权限位图','00','0');

--身份验证多厂商模式
insert into yx_param(SHL_ID,APP_ID,PARAM_NO,PARAM_VALUE,PARAM_DESC,PARAM_TYPE,USING) values ('43100002','*','YxShenFenYanZheng|MORE_YKT_MOD','26','支持学校身份验证对接统一身份认证','00','0');
insert into yx_param(SHL_ID,APP_ID,PARAM_NO,PARAM_VALUE,PARAM_DESC,PARAM_TYPE,USING) values ('43100002','*','YxShenFenYanZheng|MORE_YKT_MOD','26','支持学校身份验证对接统一身份认证','00','0');


--新增卡片信息
insert into card_tb(Card_TPCD,CARD_NM,CARD_PIC,SHL_ID,ARBG,FONT_COLOR) values('00','学生卡','236','学校编码','透明度颜色','字体颜色');
insert into card_tb(Card_TPCD,CARD_NM,SHL_ID,ARBG,FONT_COLOR) values('00','学生卡','43100002','0570c9','FFFFFF');
insert into card_tb(Card_TPCD,CARD_NM,CARD_PIC,SHL_ID,ARBG,FONT_COLOR) values('00','学生卡','236','43100002','0570c9','FFFFFF');


select * from thdpty_App_info where shl_id='10001';

insert into thdpty_App_info ( SHL_NM,SHL_ID, APP_ID, APP_NM, TITLE, CATEGORY, key, OPEN_FLG, PAY_CHECK_FLAG, RCRD_ID_WAY, CHK_COOKIE_FLAG,Open_Qb_flag,Title_nm,ASYN_SHY, rcrd_type,YKT_CZ_WAY,Offline_code_flag,Rcrd_type_offline)
values ('学校名称', '学校编码', '渠道ID', '渠道名称', '是否有title标题栏', '01', '密钥', '开通状态', '消费检查标志', '钱包消费码获取方式', '登陆是否检查cookie','同步商户云标志', '','','一卡通充值方式位图','','','');


insert into thdpty_App_info ( SHL_NM,SHL_ID, APP_ID, APP_NM, TITLE, CATEGORY, key, OPEN_FLG, PAY_CHECK_FLAG, RCRD_ID_WAY, CHK_COOKIE_FLAG,Open_Qb_flag,Title_nm,ASYN_SHY, rcrd_type,YKT_CZ_WAY,Offline_code_flag,Rcrd_type_offline)
values ('桂东县东洛中心小学','43100002','104001','104001','0','01','hQdtECo4IINRnchrJgLyLIUHbRAqOCCD','1','1','01','1','1','','1','00','10000000000000000000000001','0','11');

select * from thdpty_App_info where shl_id in('43100002','43100003','43100004','43100005','43100006','43100007')
update thdpty_App_info  set Open_Qb_flag='0' where shl_id in('43100002','43100003','43100004','43100005','43100006','43100007')

--新增学校功能菜单
insert into SCHL_MODEL_TB(SHL_ID,APP_ID,MODEL_ID,Card_TPCD) values('学校编码','APP_ID','菜单ID','卡类型ID');

insert into SCHL_MODEL_TB(SHL_ID,APP_ID,MODEL_ID,Card_TPCD) values('43100002','104001','65','00');
insert into SCHL_MODEL_TB(SHL_ID,APP_ID,MODEL_ID,Card_TPCD) values('43100002','104001','1559','00');
insert into SCHL_MODEL_TB(SHL_ID,APP_ID,MODEL_ID,Card_TPCD) values('43100002','104001','1200','00');
insert into SCHL_MODEL_TB(SHL_ID,APP_ID,MODEL_ID,Card_TPCD) values('43100002','104001','8','00');
insert into SCHL_MODEL_TB(SHL_ID,APP_ID,MODEL_ID,Card_TPCD) values('43100002','104001','1211','00');
insert into SCHL_MODEL_TB(SHL_ID,APP_ID,MODEL_ID,Card_TPCD) values('43100002','104001','1489','00');
insert into SCHL_MODEL_TB(SHL_ID,APP_ID,MODEL_ID,Card_TPCD) values('43100003','104001','65','00');
insert into SCHL_MODEL_TB(SHL_ID,APP_ID,MODEL_ID,Card_TPCD) values('43100003','104001','1559','00');
insert into SCHL_MODEL_TB(SHL_ID,APP_ID,MODEL_ID,Card_TPCD) values('43100003','104001','1200','00');
insert into SCHL_MODEL_TB(SHL_ID,APP_ID,MODEL_ID,Card_TPCD) values('43100003','104001','8','00');
insert into SCHL_MODEL_TB(SHL_ID,APP_ID,MODEL_ID,Card_TPCD) values('43100003','104001','1211','00');
insert into SCHL_MODEL_TB(SHL_ID,APP_ID,MODEL_ID,Card_TPCD) values('43100003','104001','1489','00');
insert into SCHL_MODEL_TB(SHL_ID,APP_ID,MODEL_ID,Card_TPCD) values('43100004','104001','65','00');
insert into SCHL_MODEL_TB(SHL_ID,APP_ID,MODEL_ID,Card_TPCD) values('43100004','104001','1559','00');
insert into SCHL_MODEL_TB(SHL_ID,APP_ID,MODEL_ID,Card_TPCD) values('43100004','104001','1200','00');
insert into SCHL_MODEL_TB(SHL_ID,APP_ID,MODEL_ID,Card_TPCD) values('43100004','104001','8','00');
insert into SCHL_MODEL_TB(SHL_ID,APP_ID,MODEL_ID,Card_TPCD) values('43100004','104001','1211','00');
insert into SCHL_MODEL_TB(SHL_ID,APP_ID,MODEL_ID,Card_TPCD) values('43100004','104001','1489','00');
insert into SCHL_MODEL_TB(SHL_ID,APP_ID,MODEL_ID,Card_TPCD) values('43100005','104001','65','00');
insert into SCHL_MODEL_TB(SHL_ID,APP_ID,MODEL_ID,Card_TPCD) values('43100005','104001','1559','00');
insert into SCHL_MODEL_TB(SHL_ID,APP_ID,MODEL_ID,Card_TPCD) values('43100005','104001','1200','00');
insert into SCHL_MODEL_TB(SHL_ID,APP_ID,MODEL_ID,Card_TPCD) values('43100005','104001','8','00');
insert into SCHL_MODEL_TB(SHL_ID,APP_ID,MODEL_ID,Card_TPCD) values('43100005','104001','1211','00');
insert into SCHL_MODEL_TB(SHL_ID,APP_ID,MODEL_ID,Card_TPCD) values('43100005','104001','1489','00');
insert into SCHL_MODEL_TB(SHL_ID,APP_ID,MODEL_ID,Card_TPCD) values('43100006','104001','65','00');
insert into SCHL_MODEL_TB(SHL_ID,APP_ID,MODEL_ID,Card_TPCD) values('43100006','104001','1559','00');
insert into SCHL_MODEL_TB(SHL_ID,APP_ID,MODEL_ID,Card_TPCD) values('43100006','104001','1200','00');
insert into SCHL_MODEL_TB(SHL_ID,APP_ID,MODEL_ID,Card_TPCD) values('43100006','104001','8','00');
insert into SCHL_MODEL_TB(SHL_ID,APP_ID,MODEL_ID,Card_TPCD) values('43100006','104001','1211','00');
insert into SCHL_MODEL_TB(SHL_ID,APP_ID,MODEL_ID,Card_TPCD) values('43100006','104001','1489','00');
insert into SCHL_MODEL_TB(SHL_ID,APP_ID,MODEL_ID,Card_TPCD) values('43100007','104001','65','00');
insert into SCHL_MODEL_TB(SHL_ID,APP_ID,MODEL_ID,Card_TPCD) values('43100007','104001','1559','00');
insert into SCHL_MODEL_TB(SHL_ID,APP_ID,MODEL_ID,Card_TPCD) values('43100007','104001','1200','00');
insert into SCHL_MODEL_TB(SHL_ID,APP_ID,MODEL_ID,Card_TPCD) values('43100007','104001','8','00');
insert into SCHL_MODEL_TB(SHL_ID,APP_ID,MODEL_ID,Card_TPCD) values('43100007','104001','1211','00');
insert into SCHL_MODEL_TB(ID,SHL_ID,APP_ID,MODEL_ID,Card_TPCD) values(schlModelTb_Sequence.nextval,'43100007','104001','1489','00');


select * from SCHL_MODEL_TB where shl_id in('43100002','43100003','43100004','43100005','43100006','43100007') and id like'TM%'
select * from SCHL_MODEL_TB where  id like'00%'

UPDATE SCHL_MODEL_TB SET id = '00' || SUBSTR(id, 3) WHERE id LIKE 'TM%';


update stcust_applybankcard_info set pushStatus='2' where flowNo is not null and branchno is not null and insid is not null and staffid is not null and apply_flag='2' and status_code='0' and (pushStatus is null or pushStatus='' or pushStatus='1') and create_files='1';
update stcust_applybankcard_flow set pushStatus='2' where flowNo is not null and branchno is not null and insid is not null and staffid is not null and apply_flag='2' and status_code='0' and (pushStatus is null or pushStatus='' or pushStatus='1') and create_files='1';
update xtjk_applybankcard_flow set pushStatus='2' where flowNo is not null and branchno is not null and insid is not null and staffid is not null and apply_flag='2' and status_code='0' and (pushStatus is null or pushStatus='' or pushStatus='1') and create_files='1';


select a.flowNo||'|'||a.branchNo||'|'||a.insId||'|'||a.staffId||'|'||a.crdt_picf||'|'||a.crdt_picb||'|'||a.crdt_picf_jhr||'|'||a.crdt_picb_jhr||'|'||a.crdt_pic_gx||'|'||a.sign_person||'|'||a.sign_jhr||'|'||a.sign_khsm||'|'||a.pic_hkb1||'|'||a.pic_hkb2||'|'||a.pic_hkb3||'|'||a.applybankcard_type||'|'||a.st_crdt_pic||'|'||a.crdt_no||'|'||a.apply_id||'|'||a.bankcard_sign_dt from stcust_applybankcard_info a where a.pushStatus='2';
select b.flowNo||'|'||b.branchNo||'|'||b.insId||'|'||b.staffId||'|'||a.crdt_picf||'|'||a.crdt_picb||'|'||a.crdt_picf_jhr||'|'||a.crdt_picb_jhr||'|'||a.crdt_pic_gx||'|'||b.sign_person||'|'||b.sign_jhr||'|'||b.sign_khsm||'|'||a.pic_hkb1||'|'||a.pic_hkb2||'|'||a.pic_hkb3||'|'||b.applybankcard_type||'|'||a.st_crdt_pic||'|'||a.crdt_no||'|'||b.apply_id||'|'||b.bankcard_sign_dt from stcust_applybankcard_flow b,stcust_applybankcard_infonew a where b.pushStatus='2' and b.shl_id=a.shl_id and b.st_id=a.st_id;
select b.flowNo||'|'||b.branchNo||'|'||b.insId||'|'||b.staffId||'|'||a.crdt_picf||'|'||a.crdt_picb||'|'||a.crdt_picf_jhr||'|'||a.crdt_picb_jhr||'|'||a.crdt_pic_gx||'|'||b.sign_person||'|'||b.sign_jhr||'|'||b.sign_khsm||'|'||a.pic_hkb1||'|'||a.pic_hkb2||'|'||a.pic_hkb3||'|'||b.applybankcard_type||'|'||a.st_crdt_pic||'|'||a.crdt_no||'|'||b.apply_id||'|'||b.bankcard_sign_dt from xtjk_applybankcard_flow b,xtjk_applybankcard_infonew a where b.pushStatus='2' and b.info_id=a.id;


